﻿using Dapper;
using Microsoft.Data.Sqlite;
using System.Data;

namespace ClassLibrary_fqy_NET.SQLiteHelper
{
    /// <summary>
    /// Microsoft.Data.SQLite类操作封装
    /// </summary>
    public class MicrosoftDataSQLiteHelper
    {
        private SqliteConnection? _connection;

        /// <summary>
        /// 执行SQL语句返回值
        /// </summary>
        public int Result { get; private set; }



        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public MicrosoftDataSQLiteHelper(string connectionString)
        {
            var strConnect = new SqliteConnectionStringBuilder()
            {
                DataSource = connectionString,
                Mode = SqliteOpenMode.ReadWriteCreate
            }.ToString();
            //实例化SqliteConnection
            _connection = new SqliteConnection(strConnect);
            //打开数据库
            _connection.Open();
            //配置预写日志记录提高性能和并发性
            var walCommand = _connection.CreateCommand();
            walCommand.CommandText =
            @"
                PRAGMA journal_mode = 'wal'
            ";
            Result = walCommand.ExecuteNonQuery();
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="sql">sql执行语句</param>
        /// <returns></returns>
        public int CreateTable(string sql)
        {
            if (_connection != null)
            {
                Result = _connection.Execute(sql);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <param name="sql">sql执行语句</param>
        /// <returns></returns>
        public async Task<int> CreateTableAsync(string sql)
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 插入数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> InsertDataAsync<T>(string sql, T t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 批量插入数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> InsertDataAsync<T>(string sql, List<T> t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 插入数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> InsertDataTransactionAsync<T>(string sql, T t) where T : class
        {
            int result = 0;
            if (_connection != null)
            {
                using (var transction = _connection.BeginTransaction())
                {
                    try
                    {
                        result = await _connection.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 批量插入数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> InsertDataTransactionAsync<T>(string sql, List<T> t) where T : class
        {
            int result = 0;
            if (_connection != null)
            {
                using (var transction = _connection.BeginTransaction())
                {
                    try
                    {
                        result = await _connection.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 删除数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> DeleteDataAsync<T>(string sql, T t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 批量删除数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> DeleteDataAsync<T>(string sql, List<T> t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 更新数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> UpdateDataAsync<T>(string sql, T t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 批量更新数据(异步)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<int> UpdateDataAsync<T>(string sql, List<T> t) where T : class
        {
            if (_connection != null)
            {
                Result = await _connection.ExecuteAsync(sql, t);
                return Result;
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <returns></returns>
        public async Task<List<T>?> QueryDataAsync<T>(string sql) where T : class
        {
            if (_connection != null)
            {
                var result = await _connection.QueryAsync<T>(sql);
                return result.ToList();
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询数据(按条件查询)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public async Task<List<T>?> QueryDataAsync<T>(string sql, T t) where T : class
        {
            if (_connection != null)
            {
                var result = await _connection.QueryAsync<T>(sql, t);
                return result.ToList();
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询数据(按条件查询)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public async Task<List<T>?> QueryDataAsync<T>(string sql, object param) where T : class
        {
            if (_connection != null)
            {
                var result = await _connection.QueryAsync<T>(sql, param);
                return result.ToList();
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询数据并返回DataTable
        /// </summary>
        /// <param name="sql">sql执行语句</param>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public async Task<DataTable?> QueryDataReaderAsync(string sql, object param)
        {
            if (_connection != null)
            {
                DataTable dt = new DataTable();
                var reader = await _connection.ExecuteReaderAsync(sql, param);
                dt.Load(reader);
                return dt;
            }
            else
            {
                return null;
            }
        }



    }
}
